import os
import time
import pandas as pd
from datetime import date
import plotly.graph_objs as go
import xlwings as xw
from plotly.subplots import make_subplots
import re


class s_report:
    def __init__(self):
        self.date = None
        self.path = None
        files = os.listdir('./')  # 获取当前目录下所有文件
        for file in files:
            if 'yield' in file:
                path = os.path.abspath(file)  # 获取目标文件的绝对路径
                self.path = path
                pattern = r"\d{4}(?=\d{6}\.)"
                match = re.search(pattern, file)
                if match:
                    number = match.group()
                    self.date = number

    def get_file(self):
        df = pd.read_excel(self.path, header=0, sheet_name='Main')
        df = df[[x for x in df.columns if x.endswith('.2') or x == 'Station Name']]  # 获取目标数据
        df.columns = [x.split('.')[0] for x in df.columns]  # 切分出想要的表头
        return df

    def clear_data(self):
        print("【开始处理数据，请稍等，预计十秒内完成程序运行】")
        df = self.get_file()
        df1 = df.drop(index=[0], axis=0)  # 删除第一个行 yield标签
        # today = date.today().strftime("%m-%d")  # 得到今天的日期
        # today = str(input('请输入你要显示的日期'))
        n = df1.columns.values.tolist()  # 将列标签输出为列表处理
        df1.columns = [col if i == 0 else f'{col}-{self.date}' for i, col in enumerate(n)]  # 遍历重组列标签+日期
        list_name = df1.columns.values.tolist()  # 生成新的列标签
        print(list_name)
        list_name.pop(0)
        df1[list_name] = df1[list_name].astype('float')  # 将数据转换为浮点数，方便绘图，和判断转化为字符串
        df1 = df1.replace(0, None)  # 删除无效数据
        # df1 = df1.loc[:, (df1 != 0).any(axis=0)]  # 这将保留所有至少包含一个非零值的列，并删除所有值都为0的列。
        self.plot_one_day(df1, list_name)  # 制图
        self.plot_sation(df1, list_name)  # 制图
        df1.fillna('待开线', inplace=True)  # 填充空值
        df1 = df1.apply(lambda x: ('%.2f%%' % (x * 100)) if isinstance(x, float) else x)  # 转换为百分数字符串输出
        df1.to_excel(f'{self.date}设备线体良率匹配表.xlsx', index=False, sheet_name='yield')  # 存储到新表内

        # with pd.ExcelWriter(self.path, mode='a') as writer:
        # df1.to_excel(writer, sheet_name='yield', index=False)  #存储到旧表分表内，
        with xw.App(visible=False) as app:
            wb = app.books.open(f'{self.date}设备线体良率匹配表.xlsx')
            sht = wb.sheets[0]
            sht.autofit()
            wb.save()

    def plot_one_day(self, df1, list_name):
        print('【数据处理完成，正在绘制第一张图形】')
        fig = go.Figure()
        for index, row in zip(list_name, df1['Station Name']):
            fig.add_scatter(
                y=df1[index],
                x=df1['Station Name'],
                name=f'{index}',
                mode='markers+lines+text',
                showlegend=True,
                connectgaps=True
                # text = f'{row}'
                # hoverinfo = 'text',
                # text = list_name,
                # hovermode = 'closest'
            )

        fig.update_layout(
            title={'text': 'by设备byline daily tracking ', 'x': 0.5, "font": {"family": "STKaiti", "size": 30}, },
            xaxis={'title': 'Line/date'},
            paper_bgcolor='rgb(243, 243, 250)',
            template='seaborn',  # ggplot2,gridon,plotly_dark,seaborn,xgridoff #设置不同的模版背景
            legend_title_text='设备标识',
            yaxis=dict(
                title='SN_Yield',
                tickangle=20,  # 设置刻度值的旋转角度为45度
                # categoryorder='array',  # 设置刻度模式为线性
                # autorange = True,
                # dtick = 0.0005,
                tickvals=[0.991, 0.992, 0.993, 0.994, 0.995, 0.996, 0.997, 0.9975, 0.998, 0.99825, 0.9985, 0.99875,
                          0.999, 0.99925, 0.9995, 0.99975, 1],
                # 自定义刻度值
                ticktext=['99.10%', '99.20%', '99.30%', '99.40%', '99.50%', '99.60%', '99.70%', '99.75%', '99.80%',
                          '99.825%', '99.85%', '99.875%', '99.90%', '99.925', '99.95%', '99.975',
                          '100%'],
                tickformat=".2%",
            )
        )
        fig.show()
        fig.write_html(f"{self.date}各线体良率.html")

    def plot_sation(self, df1, list_name):
        print('【即将开始绘制第二张图片】')
        list1 = []
        list2 = []
        list3 = []
        list4 = df1['Station Name'].tolist()
        list5 = []

        for x in list4[:]:
            if x == 'CG':
                break
            list1.append(x)
            list4.remove(x)

        for s in list4[:]:
            if s == 'HSG':
                break
            list2.append(s)
            list4.remove(s)

        for m in list4[:]:
            if m == 'Pre Burn':
                break
            list3.append(m)
            list4.remove(m)

        for m in list4[:]:
            if m == 'Runin':
                break
            list5.append(m)
            list4.remove(m)

        fig2 = make_subplots(
            rows=3,
            cols=2,
            subplot_titles=("BG", "CG", 'RUNIN', "HSG", "MAIN"),
            # shared_yaxes=True  #共享y轴，
            column_widths=[0.6, 0.4]
        )
        for index, n in enumerate(list1):
            fig2.add_trace(go.Scatter(
                y=df1.iloc[index, 1:].values.astype(float),
                x=list_name,
                name=f'{n}',
                mode='markers+lines',
            ), row=1, col=1
            )
        for i in list2:
            for n, m in enumerate(df1['Station Name']):
                if i == m:
                    fig2.add_trace(go.Scatter(
                        y=df1.iloc[n, 1:].values.astype(float),
                        x=list_name,
                        name=f'{i}',
                        mode='markers+lines',
                    ), row=1, col=2)
        for i in list3:
            for n, m in enumerate(df1['Station Name']):
                if i == m:
                    fig2.add_trace(go.Scatter(
                        y=df1.iloc[n, 1:].values.astype(float),
                        x=list_name,
                        name=f'{i}',
                        mode='markers+lines', ), row=2, col=2)

        for i in list4:
            for n, m in enumerate(df1['Station Name']):
                if i == m:
                    fig2.add_trace(go.Scatter(
                        y=df1.iloc[n, 1:].values.astype(float),
                        x=list_name,
                        name=f'{i}',
                        mode='markers+lines'), row=2, col=1)
        for i in list5:
            for n, m in enumerate(df1['Station Name']):
                if i == m:
                    fig2.add_trace(go.Scatter(
                        y=df1.iloc[n, 1:].values.astype(float),
                        x=list_name,
                        name=f'{i}',
                        mode='markers+lines'), row=3, col=1)
        tickvals = [0.991, 0.992, 0.993, 0.994, 0.995, 0.996, 0.997,
                    0.9975, 0.998, 0.99825, 0.9985, 0.99875,
                    0.999, 0.99925, 0.9995, 0.99975, 1]
        ticktext = ['99.10%', '99.20%', '99.30%', '99.40%', '99.50%',
                    '99.60%', '99.70%', '99.75%', '99.80%', '99.825%',
                    '99.85%', '99.875%', '99.90%', '99.925', '99.95%',
                    '99.975', '100%']
        fig2.update_yaxes(title_text="SN_yield", row=1, col=1, tickvals=tickvals, ticktext=ticktext, tickformat=".2%")
        fig2.update_yaxes(title_text="SN_yield", row=1, col=2, tickvals=tickvals, ticktext=ticktext, tickformat=".2%")
        fig2.update_yaxes(title_text="SN_yield", row=2, col=1, tickvals=tickvals, ticktext=ticktext, tickformat=".2%")
        fig2.update_yaxes(title_text="SN_yield", row=2, col=2, tickvals=tickvals, ticktext=ticktext, tickformat=".2%")
        fig2.update_yaxes(title_text="SN_yield", row=3, col=1, tickvals=tickvals, ticktext=ticktext, tickformat=".2%")
        fig2.update_layout(height=1800, width=1600,
                           title={'text': '分段设备良率图', "font": {"family": "STKaiti", "size": 30}, },
                           template='seaborn')
        fig2.show()
        fig2.write_html(f"{self.date}分段绘制设备良率.html")


if __name__ == '__main__':
    start = time.time()
    shasta = s_report()
    shasta.clear_data()
    print(f'【程序运行结束，共计时间为{round(time.time() - start, 2)}秒，生成两张散点折线图，一个整理后表格，请查看后发送】')
